package com.aier.proxy.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.aier.admin.entity.IcCard;
import com.aier.admin.entity.mapper.IcCardRowMapper;
import com.aier.common.common.Constant;
import com.aier.common.dao.ICommonDAO;
import com.aier.entrance.common.EntranceICFinalData;
import com.aier.entrance.entity.EntranceDeviceRowMapper;
import com.aier.entrance.service.IEntranceInitCommonService;
import com.aier.proxy.comm.ProxyEquipPageSize;
import com.aier.proxy.entity.Proxy;
import com.aier.proxy.entity.ProxyCity;
import com.aier.proxy.entity.ProxySub;
import com.aier.proxy.entity.ProxyUser;
import com.aier.proxy.entity.mapper.ProxyCityRowMapper;
import com.aier.proxy.entity.mapper.ProxyRowMapper;
import com.aier.proxy.entity.mapper.ProxySubRowMapper;
import com.aier.proxy.entity.mapper.ProxyUserRowMapper;
import com.aier.proxy.service.IProxyService;
import com.aier.school.entity.School;
import com.aier.school.entity.mapper.SchoolRowMapper;

@Component("proxyService")
public class ProxyServiceImpl_J implements IProxyService {

    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    @Resource(name="entranceInitCommonService")
    private IEntranceInitCommonService entranceInitCommonService;
    
    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }
    public void setEntranceInitCommonService(
            IEntranceInitCommonService entranceInitCommonService) {
        this.entranceInitCommonService = entranceInitCommonService;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Proxy> searchProxy(Long uid) {
        return (List<Proxy>) commonDao
                .list("select * from proxy as p left join proxy_user as pu on p.pid=pu.pid where pu.uid=?",
                        new Object[] { uid }, new ProxyRowMapper());
    }
    
    /**
     * 查询代理商及其所代理城市
     * @param uid:用户id
     * @return
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<Proxy> searchProxyAndCityByUid(Long uid) {
        return (List<Proxy>) commonDao
                .list("select p.*,pc.cid from proxy p left join proxy_user  pu " +
                        "on pu.pid=p.pid left join proxy_city as pc on p.pid=pc.pid where pu.uid=?",
                        new Object[] { uid }, new ProxyRowMapper(ProxyRowMapper.PROXY_AND_PROXYCITY));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProxyCity> searchProxyCity(Integer pid) {
        return (List<ProxyCity>) commonDao
                .list("select pc.*,(select sc.name from sys_city sc where sc.cid=pc.cid) as cname from proxy_city pc where pc.pid=?",
                        new Object[] { pid }, new ProxyCityRowMapper(
                                ProxyCityRowMapper.AND_CNAME));
    }

    @Override
    public int insertProxy(String name, Long uid, String cidStr) {
        int size = commonDao.countByQuali(
                "select * from proxy_user where uid=?", new Object[] { uid });
        if (size > 0) {
            return -1;
        }
        Proxy p = new Proxy();
        p.setName(name);
        p.setPtype(ProxyEquipPageSize.PROXY_PTYPE_ONE);
        KeyHolder kh = commonDao.insert(
                ProxyRowMapper.getPreparedStatementCreator(p),
                new GeneratedKeyHolder());

        /*
         * ProxyCity proxyCity=new ProxyCity();
         * proxyCity.setPid(kh.getKey().intValue()); proxyCity.setCid(cid);
         * commonDao.insert("proxy_city", ProxyCityRowMapper.getArgs(new
         * HashMap<String, Object>(), proxyCity));
         */

        ProxyUser proxyUser = new ProxyUser();
        proxyUser.setPid(kh.getKey().intValue());
        proxyUser.setUid(uid);
        commonDao.insert("proxy_user", ProxyUserRowMapper.getArgs(
                new HashMap<String, Object>(), proxyUser));
        
        if (null != cidStr && !"".equals(cidStr)) {
            for (int i = 0; i<cidStr.split(",").length;i++) {
                ProxyCity proxyCity = new ProxyCity();
                proxyCity.setPid(kh.getKey().intValue()); 
                proxyCity.setCid(Integer.valueOf(cidStr.split(",")[i]));
                commonDao.insert("proxy_city", ProxyCityRowMapper.getArgs(
                        new HashMap<String, Object>(), proxyCity));
            }
        }
        return 1;
    }

    @Override
    public Map<String, Object> searchProCardsByCondition(int page,
            int pagesize, int sortable, Integer pid, 
            Integer ic_state, String ic_head) {
        String sql = " from ic_card as ic where ic.icid in (select icid from proxy_icard where pid=?) ";

        if (ic_head != null && !ic_head.equals("-1")) {
            sql += " and ic.ichead ='" + ic_head+"'";
        }
        if (ic_state != null && ic_state != -1) {
            if (ic_state == EntranceICFinalData.IC_STATE_PROXY) {
                sql += " and ic.icstate =" + EntranceICFinalData.IC_STATE_PROXY;
            } else {
                sql += " and ic.icstate !=" + EntranceICFinalData.IC_STATE_PROXY;
            }
        }

        String sql_first = sql;
        switch (sortable) {
            case CARD_SORTABLE_NUMBER:
                sql += " order by ic.ichead,ic.icbody";
                break;
            case CARD_SORTABLE_NUMBER_DESC:
                sql += " order by ic.ichead,ic.icbody desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select ic.* "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { pid }, new Object[] { pid },
                        new IcCardRowMapper(IcCardRowMapper.AND_TIMESTR));
    }

    @Override
    public Map<String, Object> searchProCardsByCondition(int page,
            int pagesize, int sortable, int pid, 
            Integer area_id, Integer school_id, Integer ic_state,
            String ic_head, Integer card_state) {
        String sql = " from ic_card as ic where ic.icid in (select icid from proxy_icard where pid=?) ";

        if (area_id != null && area_id != -1) {
            sql += " and ic.icid in (select icid from school_icard where sid in (select sid from school where cid=" + area_id+")) ";
        }
        if (school_id != null && school_id != -1) {
            sql += " and ic.icid in (select icid from school_icard where sid=" + school_id+") ";
        }
        if (ic_head != null && !ic_head.equals("-1")) {
            sql += " and ic.ichead ='" + ic_head+"'";
        }
        if (ic_state != null && ic_state != -1) {
            if (ic_state == EntranceICFinalData.IC_STATE_PROXY) {
                sql += " and ic.icstate =" + EntranceICFinalData.IC_STATE_PROXY;
            } else {
                sql += " and ic.icstate !=" + EntranceICFinalData.IC_STATE_PROXY;
            }
        }
        if (card_state != null && card_state != -1) {
            if (card_state == EntranceICFinalData.IC_STATE_USER_ON) {
                sql += " and ic.icstate =" + EntranceICFinalData.IC_STATE_USER_ON;
            } else {
                sql += " and ic.icstate !=" + EntranceICFinalData.IC_STATE_USER_ON;
            }
        }
        
        String sql_first = sql;
        switch (sortable) {
            case CARD_SORTABLE_NUMBER:
                sql += " order by ic.ichead,ic.icbody";
                break;
            case CARD_SORTABLE_NUMBER_DESC:
                sql += " order by ic.ichead,ic.icbody desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select ic.* "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { pid }, new Object[] { pid },
                        new IcCardRowMapper(IcCardRowMapper.AND_TIMESTR));
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<IcCard> searchProCardUse(Integer pid, String body_begin,
            String body_end, String ic_head_add) {
        String sql = "select ic.* from ic_card ic where ic.icid in (select icid from proxy_icard where pid=?) " +
        		"and ic.icbody >=? and ic.icbody <=? and ic.icstate!=10 and ic.ichead=?";
        return (List<IcCard>) commonDao.list(sql, new Object[] {pid,body_begin,body_end,ic_head_add },new IcCardRowMapper());

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<IcCard> searchProCards(Integer pid) {
        String sql = "select ic.* from ic_card as ic where  ic.icid in (select icid from proxy_icard where pid=?) GROUP BY ic.ichead";
        return (List<IcCard>) commonDao.list(sql, new Object[] {pid }, new IcCardRowMapper(
                IcCardRowMapper.AND_HEADCHOOSE));
    }

    @Override
    public Map<String, Object> searchEntDevicesByCondition(int page,
            int pagesize, int sortable, int pid,
            Integer school_id, Integer dev_state, Integer poxystate, String sn) {
        String sql = " FROM entrance_device ed where ed.pid=? and ed.state!=?";

        if (sn != null && !sn.equals("")){
            sql += " and ed.sn =" + sn;
        }
        if (school_id != null && school_id != -1) {
            sql += " and ed.sid =" + school_id;
        }
        if (dev_state != null && dev_state != -1) {
            sql += " and ed.state =" + dev_state;
        }
        if (poxystate != null && poxystate != -1) {
            if (poxystate == EntranceICFinalData.ENTRANCE_STATE_SCHOOL) {
                sql += " and ed.poxystate =" + EntranceICFinalData.ENTRANCE_STATE_SCHOOL;
            } else {
                sql += " and ed.poxystate !=" + EntranceICFinalData.ENTRANCE_STATE_SCHOOL;
            }
        }

        String sql_first = sql;
        switch (sortable) {
            case ENTRANCE_SORTABLE_SN:
                sql += " order by ed.sn";
                break;
            case ENTRANCE_SORTABLE_SN_DESC:
                sql += " order by ed.sn desc";
                break;
        }
        return commonDao
                .queryPage(
                        "SELECT ed.*,(select name from school where sid=ed.sid) as school_name," +
                        "(select sn from video_device where vid=ed.vdid) as video_sn "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { pid,EntranceICFinalData.ENTRANCE_STATE_DEL }, 
                        new Object[] { pid,EntranceICFinalData.ENTRANCE_STATE_DEL},
                        new EntranceDeviceRowMapper(EntranceDeviceRowMapper.AND_SCHOOL_NAME));
    }

    /**
     * 查询代理商下拥有学校
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<School> searchSchoolsByPid(Integer pid) {
        
        return (List<School>) commonDao.list("select * from school  where pid=?", new Object[]{pid}, new SchoolRowMapper());
    }
    /**
     * 更新门禁信息
     */
    @Override
    public int updateEntranceByEid(Long eid, Short state, Short vdstate,
            Integer sid) {
        if(sid==-1){
            sid = null;
        }
        if (commonDao
                .update("update entrance_device set state=?,vdstate=?,sid=? where eid=?",
                        new Object[] {state,vdstate,sid,eid}) > 0) {
            entranceInitCommonService.initEntrance();
            return Constant.num_10001;
        } else {
            return Constant.num_10000;
        }
    }
    /**
     * 设置门禁报废
     */
    @Override
    public int updateEntranceToScrappedByEid(Long eid, Short state) {
        if (commonDao
                .update("update entrance_device set state=? where eid=?",
                        new Object[] {state,eid}) > 0) {
            entranceInitCommonService.initEntrance();
            return Constant.num_10001;
        } else {
            return Constant.num_10000;
        }
    }

    @Override
    public Map<String, Object> searchSubProxys(int page, int pagesize, int pid) {
        String sql = " from proxy as p where p.pid in (select sub_pid from proxy_sub where pid=?) ";

        String sql_first = sql;

        return commonDao
                .queryPage(
                        "select p.* "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { pid }, new Object[] { pid },
                        new ProxyRowMapper());
    }

    @Override
    public Proxy searchProxyByPid(int pid) {
        String sql = "SELECT p.*, u.account,u.phone,u.email from proxy p LEFT JOIN proxy_user pu ON p.pid = pu.pid " +
        		"LEFT JOIN `user` u ON u.uid = pu.uid where p.pid=?";
        return (Proxy)commonDao.listByObject(sql, new Object[] {pid},
                new ProxyRowMapper());
    }

    @Override
    public int insertSubProxy(int pid, int sub_ptype, String name_add,
            int cid_add,Long uid_add) {
        Proxy p = new Proxy();
        p.setName(name_add);
        p.setPtype(sub_ptype);
        KeyHolder kh = commonDao.insert(
                ProxyRowMapper.getPreparedStatementCreator(p),
                new GeneratedKeyHolder());

        
        ProxyCity proxyCity=new ProxyCity();
        proxyCity.setPid(kh.getKey().intValue()); 
        proxyCity.setCid(cid_add);
        commonDao.insert("proxy_city", ProxyCityRowMapper.getArgs(
                new HashMap<String, Object>(), proxyCity));
         

        ProxyUser proxyUser = new ProxyUser();
        proxyUser.setPid(kh.getKey().intValue());
        proxyUser.setUid(uid_add);
        commonDao.insert("proxy_user", ProxyUserRowMapper.getArgs(
                new HashMap<String, Object>(), proxyUser));
        
        ProxySub proxySub = new ProxySub();
        proxySub.setPid(pid);
        proxySub.setSubPid(kh.getKey().intValue());
        commonDao.insert("proxy_sub", ProxySubRowMapper.getArgs(
                new HashMap<String, Object>(), proxySub));
        
        return 1;
    }

    @Override
    public int deleteProxy(int pid_delete) {
        commonDao.update("delete from user where uid=(select uid from proxy_user where pid=?)",  new Object[] { pid_delete });
        commonDao.update("delete from proxy where pid=?",  new Object[] { pid_delete });
        return 1;
    }

    @Override
    public int searchProHeadNum(int pid, String ic_head_add) {
        String sql = "select count(*) as num from ic_card  where icstate=? and ichead=? " +
                "and icid in (select icid from proxy_icard where pid=?)";
        int s = 0;
        Integer tmp = (Integer) commonDao
                .listByObject(sql,
                        new Object[] { EntranceICFinalData.IC_STATE_PROXY,ic_head_add, pid},
                        new RowMapper<Integer>() {
                            @Override
                            public Integer mapRow(ResultSet rs, int arg1)
                                throws SQLException {
                                return rs.getInt("num");
                            }
                        });
        if(tmp!=null){
            s = tmp.intValue();
        }
        return s;
    }

    @Override
    public int insertSubCard(int pid, int pid_sub, String ic_head_add,
            String body_begin, String body_end) {
        if(commonDao.update("UPDATE proxy_icard SET pid=? WHERE pid=? AND icid in " +
        		"(select icid from ic_card where ichead=? and icbody>=? and icbody<=?);",  
                new Object[] { pid_sub,pid,ic_head_add,body_begin,body_end })>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int updateBackSubCard(int pid, int pid_sub, String ic_head_add,
            String body_begin, String body_end) {
        if(commonDao.update("UPDATE proxy_icard SET pid=? WHERE pid=? " +
        		"AND icid in (select icid from ic_card where ichead=? and icbody>=? and icbody<=?);",  
                new Object[] { pid,pid_sub,ic_head_add,body_begin,body_end })>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public Map<String, Object> searchProxyList(Integer cid, String account, String name, 
            int page, int page_size, int sortable) {
        String sql = "SELECT DISTINCT p.*, u.account, u.email,u.phone from proxy p LEFT JOIN proxy_user pu ON pu.pid = p.pid " +
        		"LEFT JOIN `user` u ON u.uid = pu.uid LEFT JOIN proxy_city pc ON pc.pid = p.pid";
        
        List<Object> largcount = new ArrayList<Object>();
        if (null!=name && !"".equals(name)) {
            sql += " where p.`name` like ?";
            largcount.add("%" + name + "%");
            if (cid != null && cid != -1) {
                sql += " and FIND_IN_SET(pc.cid, getChildLst(?))";
                largcount.add(cid);
            }
            if (null!=account && !"".equals(account)) {
                sql += " and u.account like ?";
                largcount.add("%" + account + "%");
            }
        } else {
            if (cid != null && cid != -1) {
                sql += " where FIND_IN_SET(pc.cid, getChildLst(?))";
                largcount.add(cid);
                if (null!=account && !"".equals(account)) {
                    sql += " and u.account like ?";
                    largcount.add("%" + account + "%");
                }
            } else {
                if (null!=account && !"".equals(account)) {
                    sql += " where u.account like ?";
                    largcount.add("%" + account + "%");
                }
            }
        }
        
        switch (sortable) {
            case IProxyService.ENTRANCE_SORTABLE_SN:
                sql += " order by p.pid";
                break;
            case IProxyService.ENTRANCE_SORTABLE_SN_DESC:
                sql += " order by p.pid desc";
                break;
        }
        return commonDao.queryPage(sql,
                page, page_size, largcount,
                new ProxyRowMapper());
    }

    @Override
    public Proxy searchProxyByUid(Long uid) {
        return (Proxy) commonDao
                .listByObject("select * from proxy as p left join proxy_user as pu on p.pid=pu.pid where pu.uid=?",
                        new Object[] { uid }, new ProxyRowMapper());
    }


    @Override
    public int updateProxyName(Long uid, String name) {
        return commonDao.update("update proxy set name=? where pid=(select pid from proxy_user where uid=?)",new Object[]{name,uid});
    }


    
    @Override
    public int updateProxyInfo(Integer pid, String email, String phone,
        String name, String cidStr) {
        Long uid = ((ProxyUser)commonDao.listByObject("select * from proxy_user where pid = ?", new Object[]{pid},new ProxyUserRowMapper())).getUid();
        commonDao.update("update user set email=? where uid = ?",new Object[] {email,uid});
        
        //修改代理商名称
        commonDao.update("update proxy set name=? where pid=?",new Object[]{name,pid});
        
        //修改代理区域
        commonDao.update("delete from proxy_city where pid=?",new Object[] { pid });
        if (null != cidStr && !"".equals(cidStr)) {
            for (int i = 0; i<cidStr.split(",").length;i++) {
                ProxyCity proxyCity = new ProxyCity();
                proxyCity.setPid(pid); 
                proxyCity.setCid(Integer.valueOf(cidStr.split(",")[i]));
                commonDao.insert("proxy_city", ProxyCityRowMapper.getArgs(
                        new HashMap<String, Object>(), proxyCity));
            }
        }
        return 1;
    }
    


}
